#-*- coding:utf-8 -*-

import thread,sys,time
import MySQLdb
import hashlib
import datetime,time
from debug import logprint
#reload(sys) 
#sys.setdefaultencoding('utf8')

db_name='dmfund'
db_user='ccsdev'
db_passwd = "czacc603+"

conn = MySQLdb.connect(host="localhost",
                       port=3306,
                       user=db_user,
                       passwd=db_passwd,
                       db=db_name,
                       charset="utf8")

period_dict={'w':-7,
             'm':-30,
             '3m':-60,
             '6m':-180,
             'y':-365,
             }

#db = web.database(dbn='mysql', db='blog', user='justin')
### 执行sql命令，创建table test

today=datetime.date.today()

global lock
lock = thread.allocate_lock()

def sql_excute(sql):
    lock.acquire() #
    conn.ping(True)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    result=cursor.fetchall()
    lock.release()
    
    return result


def dry_run_sql(sql):
    lock.acquire() #
    conn.ping(True)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    lock.release()
    

def init_symb_list(symb_dict_list,type):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    #判断标题，如存在则不插入
    sql = "insert ignore into tbl_symb_list (symb,cname,type) values (%s,%s,%s)"
    for stock in symb_dict_list:
        param = (stock['symb'],#1
                 stock['name'],#2,
                 type,
                 )
        lock.acquire() 
        cursor.execute(sql,param)
        conn.commit()
        lock.release() 
"""
   web_db areas
"""
def webget_info_from_pool():
    sql="select code,name from tbl_dmfund_pool where status=1"
    poolinfo=sql_excute(sql)
    
    return poolinfo

def webget_rank_bycode(code,num):
    sql="select date,rate from tbl_dmfund_daily_rate where code='%s' order by date DESC limit %s"%(code,num)
    rates=sql_excute(sql)
    
    return rates

def webget_pm_bycode(code,num,type):
    sql="select date,%s from tbl_dmfund_daily_quote where code='%s' order by date DESC limit %s"%(type,code,num)
    pmr=sql_excute(sql)
    
    return pmr
    
    
# end of web_db areas

def get_code_need():
    sql="select code from tbl_dmfund_pool where status=1"
    code_need=sql_excute(sql)
    
    return code_need
    
def get_tlpm_week_ranks(code,num):
    sql="select tlpm_week from tbl_dmfund_daily_quote where code='%s' order by date DESC limit %s"%(code,num)
    logprint(sql)
    tlpm_week_ranks=sql_excute(sql)
    
    return tlpm_week_ranks

def get_tlpm_month_ranks(code,num):
    sql="select tlpm_month from tbl_dmfund_daily_quote where code='%s' order by date DESC limit %s"%(code,num)
    #logprint(sql)
    tlpm_month_ranks=sql_excute(sql)
    
    return tlpm_month_ranks
    

def update_daily_quote(dblist):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    sql="REPLACE INTO tbl_dmfund_daily_quote (code,date,quote_hash,cvalue,zdzf_cyear,zdzf_week,zdzf_month,zdzf_3month,zdzf_6month,zdzf_year,tlpm_cyear,\
    tlpm_week,tlpm_month,tlpm_3month,tlpm_6month,tlpm_year) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    
    #print sql
    for fdata in dblist:
        quote_hash=hashlib.new("md5", fdata['code']+fdata['date']).hexdigest() 
        param=(fdata['code'],
               fdata['date'],
               quote_hash,
               fdata['cvalue'],
               fdata['zdzf_cyear'],
               fdata['zdzf_week'],
               fdata['zdzf_month'],
               fdata['zdzf_3month'],
               fdata['zdzf_6month'],
               fdata['zdzf_year'],
               fdata['tlpm_cyear'],
               fdata['tlpm_week'],
               fdata['tlpm_month'],
               fdata['tlpm_3month'],
               fdata['tlpm_6month'],
               fdata['tlpm_year'],
               )
        lock.acquire()
        cursor.execute(sql,param)
        conn.commit()
        lock.release() 


def get_price_bycode(code):
    sql="select cvalue,date from tbl_dmfund_daily_quote where code='%s' order by date DESC limit 1"%code
    price_date=sql_excute(sql)
    
    return price_date
    
def update_daily_rate(code,rate):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)

    rate_hash=hashlib.new("md5", str(code)+str(today)).hexdigest()
    
    sql="replace into tbl_dmfund_daily_rate (code,date,rate_hash,rate) values (%s,%s,%s,%s)"
    param=(code,today,rate_hash,rate)
    lock.acquire()
    cursor.execute(sql,param)
    conn.commit()
    lock.release()

def get_rates_bycode(code,num):
    sql="select rate from tbl_dmfund_daily_rate where code='%s' order by date DESC limit %s"%(code,num)
    rates=sql_excute(sql)
    
    return rates
    

def trade_trig(tdata):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    #sql="INSERT INTO tbl_dmfund_trade_record (code,gid,code_group,direction,price,qty,date) VALUES (%s,%s,%s,%s,%s,%s,%s) on duplicate key update price=''"
    sql="REPLACE INTO tbl_dmfund_trade_record (code,gid,code_group,direction,price,qty,date) VALUES (%s,%s,%s,%s,%s,%s,%s)"
    
    param=(tdata['code'],
           tdata['group'],
           tdata['code_group'],
           tdata['direction'],
           tdata['price'],
           tdata['qty'],
           tdata['date'],
           )
    
    lock.acquire()
    cursor.execute(sql,param)
    conn.commit()
    lock.release()
    
        
def buy_trig(code,date,price):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    #sql = "insert ignore into tbl_daily_mp (sy_exp,mp,date) values (%s,%s,%s)"%(sy_exp,mp,todaystr)
    sql = "insert ignore into tbl_dmfund_profile (code,date,price) values (%s,%s,%s)"
    #print sql
    param=(code,
           date,
           price,  
           )
    lock.acquire()
    cursor.execute(sql,param)
    conn.commit()
    lock.release()
    

def sell_trig(code,date,price):
    pass
     
if __name__ == "__main__":
    
    #init_free_symb_list()
    #init_symb_expdate_list()
    #init_n100_symb_list()
    #init_n100_symb_expdate_list()
    pass